﻿using WMLogistics.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Dapper;
using DapperExtensions;
using DapperExtensions.Mapper;
using System.Reflection;
using DapperExtensions.Sql;
using System.Data;
using Logistics.Model;

namespace WMLogistics_Dao
{
    public class WMBidBaseDao
    {
        private SqlGeneratorImpl sqlGenerator;
        public WMBidBaseDao()
        {
            var config = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new MySqlDialect());
            sqlGenerator = new SqlGeneratorImpl(config);
            DapperExtensions.DapperExtensions.Configure(config);
        }

        /// <summary>
        /// 标单管理新增数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int BidBaseAdd(wuliu_wm_bid_base model)
        {
            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Insert<wuliu_wm_bid_base>(model);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int BidBaseUpdate(wuliu_wm_bid_base model)
		{
			 using (var conn = ConnectionHelper.OpenConnection())
				 {
					StringBuilder str=new StringBuilder();
					StringBuilder strSql=new StringBuilder();
					strSql.Append("update wuliu_wm_bid_base set ");
					if(model.start_city_id.IfNotNull())
						strSql.Append("start_city_id=@start_city_id,");
					if(model.start_address.IfNotNull())
						strSql.Append("start_address=@start_address,");
					if(model.end_gk.IfNotNull())
						strSql.Append("end_gk=@end_gk,");
					if(model.end_address.IfNotNull())
						strSql.Append("end_address=@end_address,");
					if(model.start_bid_time.IfNotNull())
						strSql.Append("start_bid_time=@start_bid_time,");
					if(model.end_bid_time.IfNotNull())
						strSql.Append("end_bid_time=@end_bid_time,");
					if(model.consignee_time.IfNotNull())
						strSql.Append("consignee_time=@consignee_time,");
					if(model.receipt_time.IfNotNull())
						strSql.Append("receipt_time=@receipt_time,");
					if(model.balance.IfNotNull())
						strSql.Append("balance=@balance,");
					if(model.information.IfNotNull())
						strSql.Append("information=@information,");
					if(model.product_name.IfNotNull())
						strSql.Append("product_name=@product_name,");
					if(model.state.IfNotNull())
						strSql.Append("state=@state,");
					if(model.company_id.IfNotNull())
						strSql.Append("company_id=@company_id,");
					if(model.account_id.IfNotNull())
						strSql.Append("account_id=@account_id,");
					if(model.pickup_state.IfNotNull())
						strSql.Append("pickup_state=@pickup_state,");
					if(model.pickup_des.IfNotNull())
						strSql.Append("pickup_des=@pickup_des,");
					if(model.price.IfNotNull())
						strSql.Append("price=@price,");
					if(model.price1.IfNotNull())
						strSql.Append("price1=@price1,");
					if(model.tax_price1.IfNotNull())
						strSql.Append("tax_price1=@tax_price1,");
					if(model.tax1.IfNotNull())
						strSql.Append("tax1=@tax1,");
					if(model.price2.IfNotNull())
						strSql.Append("price2=@price2,");
					if(model.tax_price2.IfNotNull())
						strSql.Append("tax_price2=@tax_price2,");
					if(model.tax2.IfNotNull())
						strSql.Append("tax2=@tax2,");
					if(model.price3.IfNotNull())
						strSql.Append("price3=@price3,");
					if(model.tax_price3.IfNotNull())
						strSql.Append("tax_price3=@tax_price3,");
					if(model.tax3.IfNotNull())
						strSql.Append("tax3=@tax3,");
					if(model.price4.IfNotNull())
						strSql.Append("price4=@price4,");
					if(model.wuliu_id.IfNotNull())
						strSql.Append("wuliu_id=@wuliu_id,");
					if(model.bid_time.IfNotNull())
						strSql.Append("bid_time=@bid_time,");
					if(model.bid_pickup_time.IfNotNull())
						strSql.Append("bid_pickup_time=@bid_pickup_time,");
					if(model.wuliu_name.IfNotNull())
						strSql.Append("wuliu_name=@wuliu_name,");
					if(model.danger.IfNotNull())
						strSql.Append("danger=@danger,");
					if(model.number.IfNotNull())
						strSql.Append("number=@number,");
					if(model.remark.IfNotNull())
						strSql.Append("remark=@remark,");
					if(model.wait_push.IfNotNull())
						strSql.Append("wait_push=@wait_push,");
					if(model.staff.IfNotNull())
						strSql.Append("staff=@staff,");
					if(model.del_remark.IfNotNull())
						strSql.Append("del_remark=@del_remark,");
					if(model.stowage.IfNotNull())
						strSql.Append("stowage=@stowage,");
					if(model.product_id.IfNotNull())
						strSql.Append("product_id=@product_id,");
					if(model.product_size.IfNotNull())
						strSql.Append("product_size=@product_size,");
					if(model.product_weight.IfNotNull())
						strSql.Append("product_weight=@product_weight,");
					if(model.product_unit.IfNotNull())
						strSql.Append("product_unit=@product_unit,");
					if(model.tax_whether.IfNotNull())
						strSql.Append("tax_whether=@tax_whether,");
					if(model.fk.IfNotNull())
						strSql.Append("fk=@fk,");
					if(model.bid_no.IfNotNull())
						strSql.Append("bid_no=@bid_no,");
					if(model.start_city_name.IfNotNull())
						strSql.Append("start_city_name=@start_city_name,");
					if(model.factory_time.IfNotNull())
						strSql.Append("factory_time=@factory_time,");
					if(model.del_flag.IfNotNull())
						strSql.Append("del_flag=@del_flag,");
					if(model.company_no.IfNotNull())
						strSql.Append("company_no=@company_no,");
					if(model.account.IfNotNull())
						strSql.Append("account=@account,");
					if(model.company_name.IfNotNull())
						strSql.Append("company_name=@company_name,");
					if(model.wuliu_no.IfNotNull())
						strSql.Append("wuliu_no=@wuliu_no,");
					if(model.wuliu_type.IfNotNull())
						strSql.Append("wuliu_type=@wuliu_type,");
					if(model.staff_phone.IfNotNull())
						strSql.Append("staff_phone=@staff_phone,");
					if(model.bid_sel.IfNotNull())
						strSql.Append("bid_sel=@bid_sel,");
					if(model.auto_seltime.IfNotNull())
						strSql.Append("auto_seltime=@auto_seltime,");
					if(model.price_mode.IfNotNull())
						strSql.Append("price_mode=@price_mode,");
					if(model.nopickup_price.IfNotNull())
						strSql.Append("nopickup_price=@nopickup_price,");
					if(model.empty_price.IfNotNull())
						strSql.Append("empty_price=@empty_price,");
					if(model.bidoff_price.IfNotNull())
						strSql.Append("bidoff_price=@bidoff_price,");
					if(model.contacts.IfNotNull())
						strSql.Append("contacts=@contacts,");
					if(model.contacts_phone.IfNotNull())
						strSql.Append("contacts_phone=@contacts_phone,");
					if(model.flag.IfNotNull())
						strSql.Append("flag=@flag,");
					if(model.Operator.IfNotNull())
						strSql.Append("operator=@operator,");
					if(model.operator_time.IfNotNull())
						strSql.Append("operator_time=@operator_time,");
					if(model.start_city_id_x.IfNotNull())
						strSql.Append("start_city_id_x=@start_city_id_x,");
					if(model.pickback_photo_flag.IfNotNull())
						strSql.Append("pickback_photo_flag=@pickback_photo_flag,");
					if(model.flownum_max.IfNotNull())
						strSql.Append("flownum_max=@flownum_max,");
					if(model.bid_minnum.IfNotNull())
						strSql.Append("bid_minnum=@bid_minnum,");
					if(model.gx1.IfNotNull())
						strSql.Append("gx1=@gx1,");
					if(model.gx2.IfNotNull())
						strSql.Append("gx2=@gx2,");
					if(model.gx3.IfNotNull())
						strSql.Append("gx3=@gx3,");
					if(model.price_type.IfNotNull())
						strSql.Append("price_type=@price_type");
					str.Append(strSql.ToString().TrimEnd(','));
					str.Append(" where bid_id=@bid_id ");
					return conn.Execute(str.ToString(), model);
				}
		}

     


        /// <summary>
        /// 竟标开始
        /// </summary>
        /// <returns></returns>
        public List<wuliu_wm_bid_base> GetAutoStartBidList(int datenum)
        {
            IList<ISort> sort = new List<ISort>
                                    {
                                        Predicates.Sort<wuliu_wm_bid_base>(p => p.start_bid_time,false)
                                    };
            using (var conn = ConnectionHelper.OpenConnection())
            {
                var pretime = Predicates.Field<wuliu_wm_bid_base>(f => f.start_bid_time, Operator.Lt, datenum);
                var statepre = Predicates.Field<wuliu_wm_bid_base>(f => f.state, Operator.Eq, 1);
                var pre = Predicates.Group(GroupOperator.And, pretime, statepre);
                return conn.GetList<wuliu_wm_bid_base>(pre, sort).ToList();
            }
        }
        /// <summary>
        ///  竟标结束
        /// </summary>
        /// <param name="datenum"></param>
        /// <returns></returns>
        public List<wuliu_wm_bid_base> GetAutoEndBidList(int datenum)
        {
            IList<ISort> sort = new List<ISort>
                                    {
                                        Predicates.Sort<wuliu_wm_bid_base>(p => p.end_bid_time,false)
                                    };
            using (var conn = ConnectionHelper.OpenConnection())
            {
                var pretime = Predicates.Field<wuliu_wm_bid_base>(f => f.end_bid_time, Operator.Le, datenum);
                var statepre = Predicates.Field<wuliu_wm_bid_base>(f => f.state, Operator.Eq, 10);
                var pre = Predicates.Group(GroupOperator.And, pretime, statepre);
                return conn.GetList<wuliu_wm_bid_base>(pre, sort).ToList();
            }
        }

        /// <summary>
        /// 得到标单的实体集合
        /// </summary>
        public List<wuliu_wm_bid_base> GetBidBaseList(int companyid)
        {
            IList<ISort> sort = new List<ISort>
                                    {
                                        Predicates.Sort<wuliu_wm_bid_base>(p => p.bid_id,false)
                                    };
            using (var conn = ConnectionHelper.OpenConnection())
            {
                BetweenValues values = new BetweenValues();
                values.Value1 = 1;
                values.Value2 = 20;
                var prestate = Predicates.Between<wuliu_wm_bid_base>(f => f.state, values);
                var del_flag_pre = Predicates.Field<wuliu_wm_bid_base>(f => f.del_flag, Operator.Eq, 1);
                var company = Predicates.Field<wuliu_wm_bid_base>(f => f.company_id, Operator.Eq, companyid);
                var pre = Predicates.Group(GroupOperator.And, del_flag_pre, prestate, company);
                return conn.GetList<wuliu_wm_bid_base>(pre, sort).ToList();
            }
        }


        public PageData<DataTable> GetContractList(string number,  int times , int pageIndex, int pageSize)
        {

            IList<ISort> sort = new List<ISort>
                                    {
                                        Predicates.Sort<wuliu_contract_base>(p => p.id,true)
                                    };
            var sql = @" from wuliu_wm_bid_base where (number like '%" + number + "%' or bid_id like '%" + number + "%') and FROM_UNIXTIME(start_bid_time) >= DATE_SUB(CURDATE(),INTERVAL " + times + " MONTH) and FROM_UNIXTIME(start_bid_time) <= CURDATE();";

            using (var conn = ConnectionHelper.OpenConnection())
            {
                var pageData = new PageData<DataTable>();
                DynamicParameters dynamicParameters = new DynamicParameters();

                var num = conn.ExecuteScalar<int>("select count(1) " + sql);

                sql += "order by bid_id DESC limit @pageIndex,@pageSize";
                //dynamicParameters.AddDynamicParams(pickParam);
                dynamicParameters.Add("pageIndex", pageIndex * pageSize);
                dynamicParameters.Add("pageSize", pageSize);
                //var count = conn.Count<wuliu_wm_bid_load>(predicate);
                var data = conn.Query(@"select bid_id,bid_no,number,consignee_time,receipt_time,CONCAT(start_city_name,start_address) as consignee_address,
CONCAT(end_city_name,end_address) as receipt_address,del_flag  " + sql, dynamicParameters);
                pageData.DataObj = data;
                pageData.TotalNum = num;
                pageData.CurrentPage = pageIndex;
                pageData.TotalPageCount = num % pageSize == 0 ? num / pageSize : num / pageSize + 1;
                return pageData;
            }
        }

        /// <summary>
        /// 得到标单的条数
        /// </summary>
        public int GetBidBaseListCount(int companyid,int state)
        {
            IList<ISort> sort = new List<ISort>
                                    {
                                        Predicates.Sort<wuliu_wm_bid_base>(p => p.bid_id,false)
                                    };
            using (var conn = ConnectionHelper.OpenConnection())
            {
                var prestate = Predicates.Field<wuliu_wm_bid_base>(f => f.state, Operator.Eq, state);
                var del_flag_pre = Predicates.Field<wuliu_wm_bid_base>(f => f.del_flag, Operator.Eq, 1);
                var company = Predicates.Field<wuliu_wm_bid_base>(f => f.company_id, Operator.Eq, companyid);
                var pre = Predicates.Group(GroupOperator.And, del_flag_pre, prestate, company);
                return conn.GetList<wuliu_wm_bid_base>(pre, sort).ToList().Count();
            }
        }

       
        /// <summary>
        /// 得到标单的实体集合
        /// </summary>
        public List<wuliu_wm_bid_base> GetUpdateBidBaseList()
        {
            IList<ISort> sort = new List<ISort>
                                    {
                                        Predicates.Sort<wuliu_wm_bid_base>(p => p.bid_id,false)
                                    };
            using (var conn = ConnectionHelper.OpenConnection())
            {
                
                return conn.GetList<wuliu_wm_bid_base>(null, sort).ToList();
            }
        }

        /// <summary>
        /// 更新标单状态
        /// </summary>
        /// <param name="bid_id"></param>
        /// <param name="del_flag"></param>
        /// <returns></returns>
        public int UpdateDelFlag(int bid_id, int del_flag)
        {
            var sql = "update wuliu_wm_bid_base set del_flag=@del_flag where bid_id=@bid_id";
            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Execute(sql, new { del_flag, bid_id });
            }
        }


        /// <summary>
        /// 更新标单状态
        /// </summary>
        /// <param name="bidid"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public int UpdateState(int bid_id, int state)
        {
            var sql = "update wuliu_wm_bid_base set state=@state where bid_id=@bid_id";
            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Execute(sql, new { state, bid_id });
            }
        }

        public int DeleteBid(int company_id, int bid_id)
        {
            var sql = "update wuliu_wm_bid_base set state=-1 where company_id=@company_id and bid_id=@bid_id";
            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Execute(sql, new { company_id, bid_id });
            }
        }

        public object GetBidInfo(int bid_id)
        {
            string sql = @"SELECT
	a.bid_id,
    a.bid_no,
	a.number,
	a.start_bid_time,
	a.end_bid_time,
	a.danger,
	a.wuliu_type,
	a.price_mode,
	a.fk,
	a.contacts,
	a.contacts_phone,
	a.consignee_time,
	CONCAT(
		a.start_city_name,
		a.start_address
	) AS consignee_address,
    a.receipt_time,
	CONCAT(a.end_city_name, a.end_address) AS receipt_address,
	a.information,
	a.remark,
	a.staff,
	a.staff_phone,
    b.name as wuliulxname,
    b.phone as wuliuphone
    FROM
	wuliu_wm_bid_base a LEFT JOIN wuliu_wm_wlcompany_base b on a.wuliu_id=b.wuliu_id  where a.bid_id=@bid_id";
            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Query(sql, new { bid_id }).FirstOrDefault();
            }
        }

        /// <summary>
        /// 标单详情
        /// </summary>
        /// <param name="bid_id"></param>
        /// <returns></returns>
        public object GetPhoneBidInfo(int bid_id)
        {
            string sql = @"SELECT
	bid_id,
    bid_no,
	number,
	start_bid_time,
	end_bid_time,
	danger,
	wuliu_type,
	price_mode,
	fk,
	contacts,
	contacts_phone,
	consignee_time,
	CONCAT(
		start_city_name,
		start_address
	) AS consignee_address,
	receipt_time,
	CONCAT(end_city_name, end_address) AS receipt_address,
	information,
	remark,
	staff,
	staff_phone
    FROM
	wuliu_wm_bid_base where bid_id=@bid_id";

            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Query(sql, new { bid_id }).FirstOrDefault();
            }
        }

        /// <summary>
        /// 手机端获取列表
        /// </summary>
        /// <param name="predicate"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public PageModel GetPhoneBidList(IPredicate predicate, int pageIndex, int pageSize)
        {
            string sql = @" from wuliu_wm_bid_base where 1=1 ";
            IList<ISort> sort = new List<ISort>
                                    {
                                        Predicates.Sort<wuliu_contract_base>(p => p.id,true)
                                    };

            using (var conn = ConnectionHelper.OpenConnection())
            {
                var pageData = new PageModel();
                DynamicParameters dynamicParameters = new DynamicParameters();
                var whereSql = GetWhereSql(predicate, dynamicParameters);
                if (!string.IsNullOrEmpty(whereSql))
                {
                    sql += " and " + whereSql;
                }
                var num = conn.ExecuteScalar<int>("select count(1) " + sql, dynamicParameters);

                sql += "order by wuliu_wm_bid_base.bid_id DESC limit @pageIndex,@pageSize";
                //dynamicParameters.AddDynamicParams(pickParam);
                dynamicParameters.Add("pageIndex", pageIndex * pageSize);
                dynamicParameters.Add("pageSize", pageSize);
                //var count = conn.Count<wuliu_wm_bid_load>(predicate);
                var data = conn.Query(@" select bid_id,bid_no,number,consignee_time,receipt_time,CONCAT(start_city_name,start_address) as consignee_address,CONCAT(end_city_name,end_address) as receipt_address " + sql, dynamicParameters);
                pageData.datamod = data;
                pageData.totalnum = num;
                pageData.currentpage = pageIndex;
                pageData.totalpagecount = num % pageSize == 0 ? num / pageSize : num / pageSize + 1;
                return pageData;
            }

        }
        private string GetWhereSql(IPredicate predicate, DynamicParameters dynamicParameters)
        {
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            var wherSql = predicate.GetSql(sqlGenerator, parameters);

            foreach (var parameter in parameters)
            {
                dynamicParameters.Add(parameter.Key, parameter.Value);
            }
            return wherSql;
        }

        public wuliu_wm_bid_base GetBid(int bid_id)
        {
            //var sql = "select * from  wuliu_wm_bid_base where bid_id=@bid_id";
            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Get<wuliu_wm_bid_base>(bid_id);
                //return conn.Query<wuliu_wm_bid_base>(sql, new { bid_id }).SingleOrDefault();
            }
        }

        public PageData<wuliu_wm_bid_base> GetBidList(object predicate, int pageIndex, int pageSize)
        {
            IList<ISort> sort = new List<ISort>
                                    {
                                        Predicates.Sort<wuliu_wm_bid_base>(p => p.bid_id,false)
                                    };
            
            using (var conn = ConnectionHelper.OpenConnection())
            {
                var count = conn.Count<wuliu_wm_bid_base>(predicate);
                var pageDate = new PageData<wuliu_wm_bid_base>();
                pageDate.Items = conn.GetPage<wuliu_wm_bid_base>(predicate, sort, pageIndex, pageSize).ToList();
                pageDate.TotalNum = count;
                pageDate.CurrentPage = pageIndex;
                pageDate.TotalPageCount = count % pageSize == 0 ? count / pageSize : count / pageSize + 1;
                return pageDate;
            }

        }

        public bool ScrapBid(wuliu_wm_bid_base bid)
        {
            using (var conn = ConnectionHelper.OpenConnection())
            {
                return conn.Update<wuliu_wm_bid_base>(bid);
            }
        }
    }
}
